<?php
class multitender_model_region extends multitender_model {

    private $sphinx_big_number = 1000000000;
    function  __construct() {
        parent::__construct();
        $this->db_tenders = & $this->conf['dbs']['tenders'];
        $this->sphinx = ADONewConnection($this->conf['db_conf']['sphinx']['dsn']);
    }

    public function getRegionText($region_id) {
        $sql = "SELECT text FROM region_text WHERE region_id=?";
        return $this->db_tenders->GetOne($sql, $region_id);
    }

    public function getLastItems($region_id) {
        $sql = "SELECT * FROM item_old, item_fresh, item_delta WHERE region_id=? ORDER BY id ASC LIMIT 0, 5";
        $all = $this->sphinx->GetAll($sql, $region_id);
        foreach ($all as &$v) {
            $v['id'] = $this->sphinx_big_number - $v['id']; // in Sphinx (M-id)
            $mass = $this->getItemName($v['id']);

            $v['name'] = $mass['name'];
            $v['date'] = $mass['date'];
            $v['date_end'] = $mass['date_end'];
            if (is_numeric($mass['type_id'])) {
                $v['type'] = $this->getTypeItem($mass['type_id']);
    	    } else {
    		$v['type'] = '&ndash;';
    	    }
        }
        return $all;
    }

    public function getTypeItem($type) {
        return $this->db_tenders->GetOne("SELECT name FROM type WHERE id=$type");
    }

    public function getTopPrice($region_id) {
        $sql = "SELECT * FROM item_old, item_fresh, item_delta WHERE region_id=? AND price < 2147483647 ORDER BY price DESC LIMIT 0, 5";
        $all = $this->sphinx->GetAll($sql, $region_id);
        foreach ($all as &$v) {
            $v['id'] = $this->sphinx_big_number - $v['id']; // in Sphinx (M-id)
            $mass = $this->getItemName($v['id']);
            $v['name'] = $mass['name'];
            $v['date'] = $mass['date'];
            $v['date_end'] = $mass['date_end'];
            if (is_numeric($mass['type_id'])) {
        	$v['type'] = $this->getTypeItem($mass['type_id']);
            } else {
        	$v['type']='&ndash;';
            }           
        }
        return $all;

    }

    private function getItemName($id) {
        return $this->db_tenders->GetRow("SELECT name,date,date_end,type_id FROM item WHERE id=$id");
    }

    public function getTotalItems($region_id) {
        
        //
        
/*        $result = $this->db_tenders->GetAll("SELECT CONCATE(YEAR(period), '-', MONTH(period)) as p, SUM(cnt) as c FROM cache_period_count GROUP BY period HAVING YE ");
        if (empty($result)) {
            
        }*/
        
         //$start = $this->db_tenders->GetOne("SELECT COUNT(*) as cnt FROM item WHERE region_id=? AND DATE(date) < DATE(DATE_SUB(CURDATE(), INTERVAL 4 MONTH))", $region_id);
         $next = $this->db_tenders->GetAll("SELECT CONCAT(YEAR(period), '-', MONTH(period)) as period1, SUM(cnt) as cnt FROM cache_period_count WHERE region_id=? GROUP BY period1", $region_id);
//         foreach ($next as &$item) {
//             $item[1] = $item[1] + $start;
//             $start = $item[1];
//         }
         return $next;
    }

     public function getAvgPrice($region_id) {
        return $this->db_tenders->GetAll("SELECT CONCAT(YEAR(period), '-', MONTH(period)) as period1, AVG(price) as price FROM cache_period_price WHERE region_id=? GROUP BY period1", $region_id);
    }
}